package com.xiaoma.verbal.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.xiaoma.verbal.baseDao.BaseDaoImpl;
import com.xiaoma.verbal.baseDao.DefaultRowMapper;
import com.xiaoma.tpo.utils.Pager;
import com.xiaoma.verbal.domain.FctQuestion;
import com.xiaoma.verbal.domain.QuClassic;
import com.xiaoma.verbal.domain.QuQuestion;
import com.xiaoma.verbal.dto.QuestionVo;

@Repository
public class QuClassicDao extends BaseDaoImpl<QuClassic> {
	private static final Logger LOG = LoggerFactory.getLogger(QuClassicDao.class);

	@SuppressWarnings("unchecked")
	public List<QuestionVo> findClassic() {
		String sql = "SELECT a.uuid,b.* FROM qu_classic a left join  qu_question b on a.question_id = b.id  order by b.question_num ";
		LOG.info("SQL:{} PARAMS:{}", sql);
		List<?> result = jdbcTemplate.query(sql, new DefaultRowMapper(QuestionVo.class, getNameHandler()));
		return (List<QuestionVo>) result;
	}

	public Long getTimeCoordinate() {
		String sql = "SELECT sum(update_time) sumTime FROM qu_classic ";
		LOG.info(sql + " PARAMS: ");
		Long result = jdbcTemplate.queryForObject(sql, new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				return rs.getLong("sumTime");
			}
		});
		return result;
	}

	public Pager queryPageList(Integer id, String qnum, String tagText, String content, Integer startIndex, Integer itemsPerPage) {
		Pager pager = new Pager();
		List<Object> params = new ArrayList<Object>();
		String sqlQuery = " ";
		String sqlCount = " ";
		sqlQuery = "select a.* ";
		String sqlTrunk = " from qu_classic d left join qu_question a  on d.question_id = a.id  left join qu_tag b on a.id = b.question_id left join  tag_table  c on b.tag_id = c.id ";
		String where = " where 1=1 ";
		String orderBy = " order by d.id desc ";
		String limit = " limit ?,? ";
		String groupBy = " group by a.id ";
		if (id != null && !"".equals(id)) {
			where = where + " and a.id = ? ";
			params.add(id);
		}
		if (qnum != null && !"".equals(qnum)) {
			where = where + " and a.question_num = upper(?) ";
			params.add(qnum.toUpperCase());
		}
		if (content != null && !"".equals(content)) {
			where = where + " and a.content like ? ";
			params.add("%" + content + "%");
		}

		if (tagText != null && !"".equals(tagText)) {
			where = where + " and c.tag_text like ? ";
			params.add("%" + tagText + "%");
		}

		sqlQuery = sqlQuery + sqlTrunk + where + groupBy + orderBy + limit;
		sqlCount = "select count(*)  from (select a.*  " + sqlTrunk + where + groupBy + " ) as a";
		LOG.info(sqlQuery + " PARAMS: " + startIndex + ", " + itemsPerPage);
		LOG.info(sqlCount);
		int totalCount = jdbcTemplate.queryForInt(sqlCount, params.toArray());

		params.add(startIndex);
		params.add(itemsPerPage);
		List<?> list = jdbcTemplate.query(sqlQuery, params.toArray(), new DefaultRowMapper(QuestionVo.class, this.getNameHandler()));
		
		pager.setList(list);
		pager.setTotalCount(totalCount);
		return pager;
	}
}
